package top.wardyan.sqlchecker.business;

import java.io.File;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.alibaba.druid.filter.stat.StatFilter;
import com.jfinal.kit.Prop;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.CaseInsensitiveContainerFactory;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.SqlServerDialect;
import com.jfinal.plugin.druid.DruidPlugin;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.file.FileReader;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.RuntimeUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.URLUtil;
import cn.hutool.crypto.SecureUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import top.wardyan.base.common.db.po.WRecord;
import top.wardyan.base.common.util.EncodingDetect;
import top.wardyan.base.db.util.ActiveRecordUtil;
//import top.wardyan.common.db.kits.DbQryKit;
import top.wardyan.common.util.DateUtil;
import top.wardyan.sqlchecker.util.DbQryKit;
/**
 * SQL检查工具
 * 步骤:
 * 生成目录
 * 还原数据库,创建需要的数据表
 * 检查脚本
 * 运行脚本
 * 
 * @author harrydeep@wardyan.top
 * 2022年10月3日
 */
@SuppressWarnings("serial")
public class SQLCheckerBusiness2 {
	
	private static final Log log = LogFactory.get();
	
	private Prop PROP = PropKit.use("config-sqlchecker.txt");
	private String ADDR = PROP.get("addr");
	private Integer PORT = PROP.getInt("port");
	private String USERNAME = PROP.get("username");
	private String PASSWORD = PROP.get("password");
	private String DBNAME = PROP.get("dbname");
	
	// private static final String BAKNAME = "dbdata/ERP数据库.QL.20220226.CLEAN.bak";
	private String BAKNAME = PROP.get("bakname");
	/** SQL文件本地存放路径 **/
	private String SQLFOLDER = PROP.get("sqlfolder");
	private String MAXAGE = PROP.get("maxage");
	private String SQLSERVER = PROP.get("sqlserver");
	private String SOFTWAREVERSION = PROP.get("softwareversion");
	/** 执行SQL和脚本以从哪个时间点开始 **/
	private long BEGINTIME = cn.hutool.core.date.DateUtil.parse(MAXAGE).getTime();
	private static final String CONFIG = "sqlchecker";
	private static final char PREFIX_char = 65279;
	private static final String PREFIX = PREFIX_char + "go"; //char d = 65279;
	
	

	String SQL_RESTORE1 = "IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') ALTER DATABASE ERPDBTEST SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";
	String SQL_RESTORE2 = "IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') DROP DATABASE ERPDBTEST";
	String SQL_RESTORE3 = "RESTORE DATABASE ERPDBTEST FROM  DISK = N'?' WITH  FILE = 1, NOUNLOAD,  STATS = 5";
	String SQL_DROPSQLFILESLIST = "IF EXISTS(SELECT 1 FROM sys.tables AS t WHERE name = 'yt_sqlfileslist') DROP TABLE dbo.yt_sqlfileslist";
	String SQL_CREATESQLFILESLIST = "CREATE TABLE dbo.yt_sqlfileslist(sID INT IDENTITY(1, 1), xAuthor NVARCHAR(100), xDate NVARCHAR(100), xName NVARCHAR(200), xPath NVARCHAR(2000), xMD5 NVARCHAR(100), xLocation NVARCHAR(500))";
	/** 检查是否同一天提交 **/
	String SQL_DUMPLICATESUBMIT = """
SELECT DISTINCT	s.xName	-- 脚本名称
		, STUFF((SELECT ',' + n.xAuthor FROM yt_sqlfileslist (NOLOCK) AS n WHERE n.xDate = s.xDate AND n.xName = s.xName GROUP BY n.xAuthor FOR XML PATH('')), 1, 1, '') AS xAuthors
		, STUFF((SELECT ',' + n.xLocation FROM yt_sqlfileslist (NOLOCK) AS n WHERE n.xDate = s.xDate AND n.xName = s.xName FOR XML PATH('')), 1, 1, '') AS xFolders
        , s.xDate
FROM dbo.yt_sqlfileslist AS s (NOLOCK)
INNER JOIN dbo.yt_sqlfileslist AS t (NOLOCK) ON	t.xName = s.xName 
												AND t.sID <> s.sID
												AND t.xDate = s.xDate
												AND t.xMD5 <> s.xMD5
												AND LEFT(t.xAuthor, 2) <> LEFT(s.xAuthor, 2)
												AND t.sID > s.sID
ORDER BY s.xDate
			""";
	
	

	Map<String, String> errors = new LinkedHashMap<String, String>();
	Map<String, String> warnings = new LinkedHashMap<String, String>();
	Map<String, String> infos = new LinkedHashMap<String, String>();
	/** 执行每个脚本的耗时, 毫秒 **/
	Map<String, Long> costs = new LinkedHashMap<String, Long>();
	

	Map<String, String> REGS_ERRORS = new HashMap<String, String>(){{
//		this.put("USE风险1", "^(use )+");
//		this.put("USE风险2", "[^\\S]use[\\s,]");
		Prop p = PropKit.use("config-sqlchecker-errors.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
	}};
	Map<String, String> REGS_WARNINGS = new HashMap<String, String>(){{
		Prop p = PropKit.use("config-sqlchecker-warnings.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
	}};
	Map<String, String> REGS_INFOS = new HashMap<String, String>(){{
		Prop p = PropKit.use("config-sqlchecker-infos.txt");
		Set<Object> names = p.getProperties().keySet();
		
		for(Object name : names) {
			String key = String.valueOf(name);
			String regex = p.get(key);
			this.put(key, regex);
		}
		
	}};
	
	
	
	
	
	
	/**
	 * 生成所需要的文件夹
	 * 备份文件放在resources目录下的dbdata目录里
	 */
	private void generateFolder() {
		

	}
	
//	String SQL_RESTORE = """
//IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') 
//BEGIN 
//    ALTER DATABASE ERPDBTEST SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
//END
//GO
//IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') 
//BEGIN 
//    DROP DATABASE ERPDBTEST
//END
//GO
//
//RESTORE DATABASE ERPDBTEST FROM  DISK = N'D:\dbbak\ERP数据库.QL.20220226.CLEAN.bak' WITH  FILE = 1, NOUNLOAD,  STATS = 5
//""";
	
	DruidPlugin dps = null;
	ActiveRecordPlugin arps = null;

	/**
	 * 重建数据库和数据表
	 */
	private void restoredb() {
		var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		
		// 备份文件改为从服务器上取
		/**
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		// System.out.println(path);
		**/
		String path = "E:\\UUERP\\SQLSERVER2022\\BAKS2022\\ERPDBTEST.clean.20241203.bak";
		Record r = Db.findFirst("select xValue from yt_setting (nolock) where sID = 150002");
		if(r != null) {
			path = r.getStr("xValue");
		}
		
		try {
			log.info("准备数据库环境...");
			String jdbcurl = String.format("jdbc:sqlserver://%s:%d; DatabaseName=%s; trustServerCertificate=true", ADDR, PORT, "master");	// 还原数据库要先连master
			DruidPlugin dp = new DruidPlugin(jdbcurl, USERNAME, PASSWORD);
			dp.addFilter(new StatFilter());
			ActiveRecordPlugin arp=new ActiveRecordPlugin(CONFIG, dp);
			arp.setContainerFactory(new CaseInsensitiveContainerFactory());
			arp.setDialect(new SqlServerDialect());	
			dp.start();
			arp.start();
			
			Db.use(CONFIG).update(SQL_RESTORE1);
			Db.use(CONFIG).update(SQL_RESTORE2);
			String s = SQL_RESTORE3.replace("?", path);
			Db.use(CONFIG).update(s);
			
			arp.stop();
			dp.stop();
			// DbKit.removeConfig("main");
			/**
			jdbcurl = String.format("jdbc:sqlserver://%s:%d; DatabaseName=%s; trustServerCertificate=true", ADDR, PORT, DBNAME);
			dps = new DruidPlugin(jdbcurl, USERNAME, PASSWORD);
			dps.addFilter(new StatFilter());
			arps=new ActiveRecordPlugin(CONFIG, dps);
			arps.setContainerFactory(new CaseInsensitiveContainerFactory());
			arps.setDialect(new SqlServerDialect());	
			dps.start();
			arps.start();
//			
			**/
			ActiveRecordUtil.init(CONFIG, 1, ADDR, PORT, DBNAME, USERNAME, PASSWORD);
			
//			dps = dp;
//			arps = arp;
//			
			Db.use(CONFIG).update(SQL_DROPSQLFILESLIST);
			Db.use(CONFIG).update(SQL_CREATESQLFILESLIST);
			// System.out.println(url);
			
			log.info("准备数据库环境完成");
		}catch(Exception e) {
			log.error(e);
		}
	}
	
	/**
	 * 从服务器上拷贝脚本
	 * 2022-10-30 先从发版本的地方拷贝, 再从未发版本的地方拷贝, 然后把
	 */
	private void copySQLs() {
		// SQL目录还不存在的话先创建目录
		var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		String[] ps = path.split("/");
		// ps = ArrayUtil.sub(ps, 0, ps.length - 1);
		ps[ps.length - 1] = SQLFOLDER;
		
		String p = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		
		// Console.log(ArrayUtil.join(ps, "/"));

		if(FileUtil.exist(p)) {
			log.info("原目录已存在,删除重来:{}", p);
			// FileUtil.mkdir(p);
			long before = System.currentTimeMillis();
			var ok = FileUtil.del(p);
			
			log.info("删除原目录完成,耗时秒:{}", (System.currentTimeMillis() - before) / 1000);
			
		}
		
		if(!FileUtil.exist(p)) {
			log.info("创建文件夹:", p);
			FileUtil.mkdir(p);
		}
		
		Record r = Db.findFirst("select xDateTime from yt_setting where sID = 150001");
		if(r != null && r.getTimestamp("xDateTime") != null) {
			MAXAGE = DateUtil.format(r.getTimestamp("xDateTime"), "yyyyMMdd");
		}
		
		// 已发版本的
		String version = "robocopy \"" + SOFTWAREVERSION + "\" \"" + p + "\"  *.sql /s /MIR /MT:4 /maxage:" + MAXAGE;
		
		log.info("执行命令:{}", version);
		long before = System.currentTimeMillis();
		String str = RuntimeUtil.execForStr (version);
		log.info("执行命令完成:{},耗时秒:{}", version, (System.currentTimeMillis() - before) / 1000);
		
		
		// String cmd = "robocopy \"\\\\server6\\sql\\MES_SQL_XH \"" + p + "\"  *.sql /s /MIR /maxage:" + MAXAGE;
		// SQLSERVER
		// 排除已打包脚本, 排除debug脚本
		String cmd = "robocopy \"" + SQLSERVER + "\" \"" + p + "\" *.sql /s /MIR /MT:4 /maxage:" + MAXAGE + "  /XD 22* 23* 24* 25* 26* 27* 28* 29* 21* 1* 2000* 3* /XF *debug*";
		
		log.info("执行命令:{}", cmd);
		before = System.currentTimeMillis();
		str = RuntimeUtil.execForStr (cmd);
		log.info("执行命令完成:{},耗时秒:{}", cmd, (System.currentTimeMillis() - before) / 1000);
		// String rs = str;
	}
	
	

	
	/**
	 * 通过正则表达式检查脚本是否匹配
	 * @return
	 */
	private Boolean checkSQLFile(String str, Map<String, String> regs, String filename) {
		Boolean rs = true;
		if(str != null && regs != null) {
			for(var reg : regs.entrySet()) {
				String name = reg.getKey();
				String rule = reg.getValue();
				List<String> ss = ReUtil.findAll(rule, str, 0);
				if(ss != null && ss.size() > 0) {
					rs = false;
					errors.put(filename, name);
					break;
				}
			}
		}
		return rs;
	}
	
	/**
	 * 把检查到的SQL脚本记录到数据库里, 方便检查同一天的重复提交文件
	 */
	private void logSQLFiles(String name, String path, String folder, File f) {
		String author = "";
		String date = "";
		
		date = String.valueOf(ReUtil.getFirstNumber(folder));
		if(date.length() > 6) {
			date = date.substring(date.length() - 6);
		}
		
		if(date != null && folder.length() > date.length()) {
			author = folder.substring(date.length());
			if(author.startsWith("_") || author.startsWith("-") || author.startsWith(" ") || author.startsWith(".")) {
				author = author.substring(1);
			}
			List<String> r = ReUtil.findAll("(.*?)[_\\./]+", author, 0);
			if(r != null && r.size() > 0) {
				author = r.get(0);
			}
		}
		//log.info("文件名:{},提交人:{},日期:{}", path, author, date);
		
		List<String> ts = ReUtil.findAll("[\\.\\[ ]([a-z][fpv]_[a-zA-Z_\\-]+?)[^a-zA-Z_\\-]", name, 0);
		// Console.log(ts);
		if(ts != null && ts.size() > 0) {
			String n = ts.get(0);
			if(n.startsWith("_") || n.startsWith("-") || n.startsWith(" ") || n.startsWith(".")) {
				n = n.substring(1);
			}
			
			if(n.endsWith("_") || n.endsWith("-") || n.endsWith(" ") || n.endsWith(".")) {
				n = n.substring(0, n.length() - 1);
			}
			String md5 = SecureUtil.md5(f);
			Db.use(CONFIG).save("yt_sqlfileslist", "sID", new Record()
					.set("xAuthor", author)
					.set("xDate", date)
					.set("xName", n)
					.set("xPath", path)
					.set("xMD5", md5)
					.set("xLocation", folder)
					);
		}else {
			// 检查是否触发器
			List<String> t = ReUtil.findAll("[\\.\\[ ](trg_[a-zA-Z_\\-]+?)[^a-zA-Z_\\-]", name, 0);
			// Console.log(ts);
			if(t != null && t.size() > 0) {
				String n = t.get(0);
				if(n.startsWith("_") || n.startsWith("-") || n.startsWith(" ") || n.startsWith(".")) {
					n = n.substring(1);
				}
				
				if(n.endsWith("_") || n.endsWith("-") || n.endsWith(" ") || n.endsWith(".")) {
					n = n.substring(0, n.length() - 1);
				}
				String md5 = SecureUtil.md5(f);
				Db.use(CONFIG).save("yt_sqlfileslist", "sID", new Record()
						.set("xAuthor", author)
						.set("xDate", date)
						.set("xName", n)
						.set("xPath", path)
						.set("xMD5", md5)
						.set("xLocation", folder)
						);
			}
		}
	}
	/**
	 * 检查同一天重复提交的脚本
	 */
	private void checkDumplicateSubmit() {
		WRecord res = DbQryKit.qry(CONFIG, SQL_DUMPLICATESUBMIT);
		// WRecord res = ProcKit.query(CONFIG, SQL_DUMPLICATESUBMIT, new HashMap<>());
		if(res != null && res.getRecords() != null && res.getRecords().size() > 0) {
			List<Record> rs = res.getRecords();
			for(Record r : rs) {
				errors.put(r.getStr("xName"), StrUtil.format("当天多人同时提交脚本,提交人:{},提交目录:{}", r.getStr("xAuthors"), r.getStr("xFolders")));
			}
		}
	}
	/**
	 * 文件排序
	 * 如果是文件夹, 就以日期来排序; 如果是文件, 则以前缀的数字来排序
	 * @param files
	 * @return
	 */
	private File[] sort(File[] files) {
		
		return Arrays.asList(files).stream().sorted(new Comparator<File>() {
			@Override
			public int compare(File f1, File f2) {
				// 文件夹一定优先于文件f
				// 
				int rs = 0;
				if(f1.isDirectory() && f2.isDirectory()) {
					// 两个都是文件夹	// 如果一个是完整的年, 另一个实际简短的年, 就把完整的年去掉前两位
					// NumberUtil.parse
					Integer date1 = ReUtil.getFirstNumber(f1.getName());
					Integer date2 = ReUtil.getFirstNumber(f2.getName());
					if(date1 == null) {
						date1 = 0;
					}
					if(date2 == null) {
						date2 = 0;
					}
					if(date1 > 999999) {
						date1 = date1 % 1000000;
					}
					if(date2 > 999999) {
						date2 = date2 % 1000000;
					}
					rs = Integer.compare(date1,  date2);
					
				}else if(f1.isDirectory() || f2.isDirectory()) {
					// 一个是文件夹
					if(f1.isDirectory()) {
						rs = -1;
					}else {
						rs = 1;
					}
				}else {
					// 两个都不是文件夹
					Integer date1 = ReUtil.getFirstNumber(f1.getName());
					Integer date2 = ReUtil.getFirstNumber(f2.getName());
					if(date1 == null) {
						date1 = 0; 
					}
					if(date2 == null) {
						date2 = 0;
					}
					rs = Integer.compare(date1,  date2);
				}
				return rs;
			}
		}).collect(Collectors.toList()).toArray(new File[] {});
		
		// return files;
	}
	
	/**
	 * 检查SQL脚本
	 * @param location
	 */
	private void listFiles(String location) {
		if(location == null) {
			var u = ResourceUtil.getResource(BAKNAME);
			// System.out.println(u);
			String path = u.getPath();
			if(path.startsWith("/")) {
				path = path.substring(1);
			}
			path = URLUtil.decode(path);
			String[] ps = path.split("/");
			// ps = ArrayUtil.sub(ps, 0, ps.length - 1);
			ps[ps.length - 1] = SQLFOLDER;
			
			location = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		}
		
		//log.info("检查目录:{}", location);

		// 排除: 以debug.sql结尾的都过滤掉
		if(location.toLowerCase().endsWith("debug") || location.toLowerCase().endsWith("error") || location.toLowerCase().endsWith("err")) {
			log.info("debug文件,跳过... {}", location);
			return;
		}
		
		// 列出下级
//		List<String> files = new ArrayList<>();
//		try {
//			files = FileUtil.listFileNames(location);
//			System.out.println(files);
//			FileUtil.files
//		}catch(Exception e) {
//			log.error(e);
//		}
//		// 怎么排序?
//		for(String fileName : files) {
//			File file = FileUtil.file(fileName);
//			if(file.isDirectory()) {
//				log.info("是目录,要查下级目录.{}", location);
//				//listFiles(location);
//			}
//		}
//		
		File file = new File(location);
		// 在这里肯定是目录
//		if(file.isDirectory()) {
//			// 是目录,列出下级目录/文件
//			Console.log(file.listFiles());
//		}
		File[] subs = file.listFiles();
		if(subs != null && subs.length > 0) {
			// 排序	// 如果是文件夹, 就以日期来排序; 如果是文件, 则以前缀的数字来排序
			subs = sort(subs);
			for(File f : subs) {
				String filename = location + "/" + f.getName();

				String fn = file.getName() + "/" + f.getName();
				
				// log.info(filename);
				if(f.isDirectory()) {
					//log.info("是目录,要查下级目录.{}", filename);
					listFiles(filename);
				}else {

					if(f.lastModified() < BEGINTIME) {
						log.info("文件的最后修改时间早于检查的起始时间,跳过:{}", fn);
						continue;
					}
					
					// 如果不是.sql文件就跳过
					if(!filename.toLowerCase().endsWith(".sql")) {
						log.info("不是SQL文件,跳过:{}", fn);
						continue;
					}
					
					// 是文件...
					//log.info("是文件:{}", filename);
					if(filename.toLowerCase().endsWith("debug.sql")) {
						log.info("debug文件,跳过... {}", filename);
						continue;
					}
					
					
					String encoding = "gb2312";
					
					if(f.length() > 1) {
						encoding = EncodingDetect.getJavaEncode(filename).toLowerCase();
					}
					FileReader fr = FileReader.create(f);
					
					
					
					// fr.readLines();
					// String texts = CollectionUtil.join(fr.readLines(), System.lineSeparator());
					String texts = fr.readString();
					log.info("文件编码:{}, 文件:{}", encoding, filename);
					
					// 如果不是gb2312,则要警告
					if(!encoding.equals("gb2312") && !encoding.equals("unicode")) {
						warnings.put(fn, "编码不是GB2312或者UTF-16LE有签名,而是:" + encoding);
					}
					
					
					
					this.logSQLFiles(f.getName(), filename, file.getName(), f);
					Boolean ok = true;
					if(ok) {
						ok = this.checkSQLFile(texts, REGS_ERRORS, fn);
					}
					if(ok) {
						ok = this.checkSQLFile(texts, REGS_WARNINGS, fn);
					}
					if(ok) {
						ok = this.checkSQLFile(texts, REGS_INFOS, fn);
					}
				}
			}
		}
		

		
	}
	
	
	
	/**
	 * 把每一个脚本都执行一次
	 * @param location
	 */
	private void execSQLFiles(String location) {

		if(location == null) {
			var u = ResourceUtil.getResource(BAKNAME);
			// System.out.println(u);
			String path = u.getPath();
			if(path.startsWith("/")) {
				path = path.substring(1);
			}
			path = URLUtil.decode(path);
			String[] ps = path.split("/");
			// ps = ArrayUtil.sub(ps, 0, ps.length - 1);
			ps[ps.length - 1] = SQLFOLDER;
			
			location = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		}
		
		//log.info("最终执行,检查目录:{}", location);

		// 排除: 以debug.sql结尾的都过滤掉
		if(location.toLowerCase().endsWith("debug") || location.toLowerCase().endsWith("error") || location.toLowerCase().endsWith("err")) {
			log.info("debug文件,跳过... {}", location);
			return;
		}
		
		// 列出下级
		File file = new File(location);
		// 在这里肯定是目录
		File[] subs = file.listFiles();
		if(subs != null && subs.length > 0) {
			subs = sort(subs);
			for(File f : subs) {
				String filename = location + "/" + f.getName();
				// log.info(filename);
				if(f.isDirectory()) {
					//log.info("最终执行,是目录,要查下级目录.{}", filename);
					execSQLFiles(filename);
				}else {
					// 是文件...
					//log.info("最终执行,是文件:{}", filename);
										
					if(f.lastModified() < BEGINTIME) {
						log.info("文件的最后修改时间早于检查的起始时间,跳过:{}", filename);
						continue;
					}

					// 如果不是.sql文件就跳过
					if(!filename.toLowerCase().endsWith(".sql")) {
						log.info("不是SQL文件,跳过:{}", filename);
						continue;
					}
					
					// 排除: 以debug.sql结尾的都过滤掉
					if(filename.toLowerCase().endsWith("debug.sql")) {
						log.info("debug文件,跳过... {}", filename);
						return;
					}
					try {
						// 编码
						String encoding = EncodingDetect.getJavaEncode(filename).toLowerCase();
						Charset c = Charset.forName(encoding);
						FileReader fr = FileReader.create(f, c);
						// 遇到GO就换文件
						List<String> sqls = new ArrayList<>();
						// sqls.add(fr.readString());
						List<String> temps = new ArrayList<>();
//						if(fr.readString().contains("lp_qry_capacity_weekly")) {
//							Console.log("HERE");
//						}
						for(String line : fr.readLines()) {
							if(line != null 
									&& (line.strip().toLowerCase().startsWith("go") || line.strip().toLowerCase().startsWith(PREFIX))
									&& !line.strip().toLowerCase().startsWith("goto")
									//&& ( line.strip().length() < 3 || line.strip().toLowerCase().startsWith("go") )
									) {
								// 换行
								String sql = CollectionUtil.join(temps, System.lineSeparator());
								sqls.add(sql);
								temps = new ArrayList<>();
							}else {
								temps.add(line);
								// temps.add(System.lineSeparator());
							}
						}
						// String texts = CollectionUtil.join(fr.readLines(), System.lineSeparator());
						// .log(texts);
						// Db.update(texts);
						
						long before = System.currentTimeMillis();
						for(String sql : sqls) {
							Integer cnt = 0;
							try {
								byte[] bs = sql.getBytes();
								if(bs.length > 3) {
									var b1 = bs[0];
									var b2 = bs[1];
									var b3 = bs[2];
									if(b1 < 0 && b2 < 0 && b3 < 0) {
										// msg += "  [文件头EFBBBF,UTF8-BOM编码]";
										sql = new String(ArrayUtil.sub(bs, 3, bs.length));
									}
								}
								// sql = sql.trim();
								
								cnt = Db.use(CONFIG).update(sql);
							}catch(Exception e) {
								e.printStackTrace();
								String msg = e.getMessage();
								if(msg.contains(":")) {
									msg = msg.substring(msg.indexOf(":") + 1);
								}
								
								
								// SQL文件的开始必然是英文字符, 不能接受中文字符! 因此BYTE里肯定是>0的
								if(msg.contains("附近有语法错误")){		// 可能是UTF8-BOM导致的
									byte[] bs = sql.getBytes();
									if(bs.length > 3) {
										var b1 = bs[0];
										var b2 = bs[1];
										var b3 = bs[2];
										if(b1 < 0 && b2 < 0 && b3 < 0) {
											msg += "  [文件头EFBBBF,UTF8-BOM编码]";
										}
									}
								}
								
								errors.put( file.getName() + "/" + f.getName(), msg.strip());
								
							}
							
							if(cnt > 0) {
								infos.put( file.getName() + "/" + f.getName(), "脚本返回行数:" + cnt);
							}
						}
						
						log.info("执行脚本完成, 脚本名:{},耗时毫秒:{}", filename, (System.currentTimeMillis() - before));
						
						costs.put(file.getName() + "/" + f.getName(), (System.currentTimeMillis() - before));
						
						Thread.sleep(10);
					}catch(Exception e) {
						e.printStackTrace();
						String msg = e.getMessage();
						if(msg.contains(":")) {
							msg = msg.substring(msg.indexOf(":") + 1);
						}
						errors.put( file.getName() + "/" + f.getName(), msg.strip());
//						if(msg.contains("附近有语法错误")){
//							Console.log("************************");
//						}
					}
				}
			}
		}
	}
	
	/**
	 * 检查SQL脚本
	 */
	private void checkfiles() {
		this.listFiles(null);
		this.checkDumplicateSubmit();
	}
	
	private void writeExcel() {
		var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);

		String[] ps = path.split("/");
		// ps = ArrayUtil.sub(ps, 0, ps.length - 1);
		// DateUtil.formatNow("_yyyy_MM_dd_HH_mm_ss");
		String fn = "SQL检查结果." + DateUtil.formatNow("_yyyy_MM_dd_HH_mm_ss") + ".xls";
		
		
		ps[ps.length - 1] = fn;
		
		String p = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		
		
		
		List<List<String>> rows = new ArrayList<>();
		rows.add(CollUtil.newArrayList("错误级别", "*"));
		List<String> row1 = CollUtil.newArrayList("路径", "说明");
		
		rows.add(row1);
		if(errors.size() > 0) {
			for(var item : errors.entrySet()) {
				List<String> row = CollUtil.newArrayList(item.getKey(), item.getValue());
				rows.add(row);
			}
		}

		rows.add(CollUtil.newArrayList("", ""));
		rows.add(CollUtil.newArrayList("", ""));

		rows.add(CollUtil.newArrayList("警告级别", "*"));
		rows.add(CollUtil.newArrayList("路径", "说明"));

		if(warnings.size() > 0) {
			for(var item : warnings.entrySet()) {
				List<String> row = CollUtil.newArrayList(item.getKey(), item.getValue());
				rows.add(row);
			}
		}
		

		rows.add(CollUtil.newArrayList("", ""));
		rows.add(CollUtil.newArrayList("", ""));
		rows.add(CollUtil.newArrayList("提示级别", "*"));
		rows.add(CollUtil.newArrayList("路径", "说明"));

		if(infos.size() > 0) {
			for(var item : infos.entrySet()) {
				List<String> row = CollUtil.newArrayList(item.getKey(), item.getValue());
				rows.add(row);
			}
		}
		
		
		ExcelWriter writer = ExcelUtil.getWriter(p);
		
		Font font = writer.createFont();
		font.setFontName("新宋体");
		// font.setFontHeight((short)11);
		font.setFontHeightInPoints((short)11);
		
		writer.getStyleSet().setFont(font, false);
		//writer.merge(row1.size() - 1, "错误级别");
		// 水平左对齐，垂直中间对齐
		writer.getStyleSet().setAlign(HorizontalAlignment.LEFT, VerticalAlignment.CENTER); 
		//writer.passCurrentRow();
		// writer.write(rows, true);
		writer.write(rows, false);
		//关闭writer，释放内存
		writer.autoSizeColumnAll();
		
		// 第二页
		writer.setSheet("脚本执行耗时");
		writer.getStyleSet().setFont(font, false);
		rows = new ArrayList<>();
		rows.add(CollUtil.newArrayList("路径", "执行耗时(毫秒)"));
		if(costs != null) {
			for(var item : costs.entrySet()) {
				List<String> row = CollUtil.newArrayList(item.getKey(), String.valueOf(item.getValue()));
				rows.add(row);
			}
		}
		writer.write(rows, false);

		writer.autoSizeColumnAll();
		writer.close();
		log.warn("写入EXCEL成功,路径:{}", p);
		
		// 打开这个EXCEL
		RuntimeUtil.exec(String.format("cmd /c start excel %s", p));
//		
		log.info("尝试打开结果文件,路径:{}", p);
	}
	
	public void go() {
		
		
		
		generateFolder();
		
		restoredb();
		
		copySQLs();
		
		
		
		this.checkfiles();
		
		this.execSQLFiles(null);

		/**
		Console.log(errors);
		Console.log(warnings);
		Console.log(infos);
		**/
		/**
		log.error("**** 脚本检查工具结果 ****");
		log.error("错误级别数:{},警告级别数:{},提示级别数:{}", errors.size(), warnings.size(), infos.size());
		if(infos.size() > 0) {
			log.error("*".repeat(120));
			log.error("提示级别清单:数量{}", infos.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : infos.entrySet()) {
				// log.error("{} {} {} {}");
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		if(warnings.size() > 0) {
			log.error("*".repeat(120));
			log.error("警告级别清单:数量{}", warnings.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : warnings.entrySet()) {
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		if(errors.size() > 0) {
			log.error("*".repeat(120));
			log.error("错误级别清单:数量{}", errors.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : errors.entrySet()) {
				// log.error("{} {} {} {}");
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		**/
		// 生成一个HTML文件 // excel
		// ExcelUtil.
		writeExcel();
	}

	private ClassLoader getClassLoader() {
		ClassLoader ret = Thread.currentThread().getContextClassLoader();
		return ret != null ? ret : getClass().getClassLoader();
	}
	

	
	
	public static void main(String[] args) {
		// 等下再读取配置
//		String jdbcurl = String.format("jdbc:sqlserver://%s:%d; DatabaseName=%s; trustServerCertificate=true", "127.0.0.1", 1433, "master");
//		DruidPlugin dp = new DruidPlugin(jdbcurl, "sa", "uuerp888");
//		dp.addFilter(new StatFilter());
//		//WallFilter wall = new WallFilter();
//		// wall.setDbType(p.get("dbType"));
//		//wall.setDbType("sqlserver");
//		//dp.addFilter(null);
//
//		ActiveRecordPlugin arp=new ActiveRecordPlugin("main", dp);
//		arp.setContainerFactory(new CaseInsensitiveContainerFactory());
//		arp.setDialect(new SqlServerDialect());	
//		dp.start();
//		arp.start();
//		
		//sql模板
		//arp.getEngine().setSourceFactory(new ClassPathSourceFactory());
		
		new SQLCheckerBusiness2().go();
		
//		arp.stop();
//		dp.stop();
		
	}
	

}
